package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.core.dao.hibernate.ReportDaoHibernate;
import com.eling.elcms.core.util.PropertyUtils;
import com.eling.elcms.travel.dao.IDailyBusinessDao;
import com.eling.elcms.travel.model.CheckOutDeal;
import com.eling.elcms.travel.model.NonMemberGroup.TourType;
import com.eling.elcms.travel.model.RoomTypePrice;
import com.eling.elcms.travel.service.IRoomTypePriceManager;
import org.apache.commons.lang3.time.DateUtils;
import org.hibernate.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.math.BigInteger;
import java.util.*;

@Repository
public class DailyBusinessDaoHibernate extends ReportDaoHibernate implements IDailyBusinessDao{
	
	@Autowired
	private IRoomTypePriceManager roomTypePriceManager;

	@Override
	public Double assReceivable(Long pkServicePoint, Date start, Date end, boolean alliance) {
		String sql = "select cii.pkCIImplement,rr.checkInPrice,gr.checkinDate,gr.checkOutDate from travel_checkinimplement cii,travel_groupreserve gr,travel_planitemgroup pg,travel_roomregister rr,travel_roompersonalInfo rp "
				+" where  cii.pkGroupReserve=gr.pkGroupReserve and gr.pkPlanItemGroup= pg.pkPlanItemGroup and cii.pkCIImplement =rp.pkCIImplement and rr.pkRoomRegister=rp.pkRoomRegister "
				+" and rr.checkInPrice is not null and cii.pkServicePoint= :pkServicePoint and gr.checkOutDate >= :start and gr.checkinDate <= :end and pg.alliance = :alliance and pg.checkInStatus='CheckIn';";
		@SuppressWarnings("unchecked")
		List<Object[]> objects = getSession().createNativeQuery(sql)
				.setParameter("pkServicePoint", pkServicePoint)
				.setParameter("start", start)
				.setParameter("end", end)
				.setParameter("alliance", alliance).list();
		return countSum(objects,start,end);
	}

	@Override
	public Double assReceived(Long pkServicePoint, Date start, Date end, boolean alliance) {
		String sql = "select sum(cos.room) from travel_checkinimplement cii,travel_groupreserve gr,travel_planitemgroup pg,travel_roomregister rr,travel_roompersonalInfo rp,travel_checkoutsettlement cos "
				+" where  cii.pkGroupReserve=gr.pkGroupReserve and gr.pkPlanItemGroup= pg.pkPlanItemGroup and cii.pkCIImplement =rp.pkCIImplement and rr.pkRoomRegister=rp.pkRoomRegister and rp.pkCheckOutSettlement=cos.pkCheckOutSettlement "
				+" and rr.checkInPrice is not null and cii.pkServicePoint= :pkServicePoint and gr.checkOutDate between :start and :end and pg.alliance = :alliance and cos.checkOutSettlement = true ;";
		Query query = getSession().createNativeQuery(sql)
				.setParameter("pkServicePoint", pkServicePoint)
				.setParameter("start", start)
				.setParameter("end", end)
				.setParameter("alliance", alliance);
		
		return query.uniqueResult()==null?0D:(Double) query.uniqueResult();
	}
	
	//计算余额
	private Double countSum(List<Object[]> objects, Date start, Date end) {
		Map<Long,Double> map=new HashMap<Long, Double>();
		for (Object[] obj : objects) {
			if(map.containsKey(((BigInteger)obj[0]).longValue())){
				if(DateUtils.isSameDay(start, end)){
					map.put(((BigInteger)obj[0]).longValue(), (Double)obj[1]);
				}else{
					Date checkin =(Date)obj[2];
					Date checkout =(Date)obj[3];
					if (checkin.before(start)) {
						map.put(((BigInteger)obj[0]).longValue(), ((Double)obj[1])*(daysOfTwoDate(start, checkout)));
					}else if (checkout.after(checkout)) {
						map.put(((BigInteger)obj[0]).longValue(), ((Double)obj[1])*(daysOfTwoDate(checkin, end)));
					}else{
						map.put(((BigInteger)obj[0]).longValue(), ((Double)obj[1])*(daysOfTwoDate(checkin, checkout)));
					}
				}
			}
		}
		Double sum =0D;
		for (Double checkInPrice : map.values()) {
			sum +=checkInPrice;
		}
		return sum;
	}

	@Override
	public Double singleReceivable(Long pkServicePoint, Date start, Date end, TourType tourType) {
		String sql = "select cii.pkCIImplement,rr.checkInPrice from travel_checkinimplement cii,travel_nonmemgroupreserve nmgr,travel_nonmembergroup nmg,travel_roomregister rr,travel_roompersonalInfo rp "
				+" where  cii.pkNonMemGroupReserve=nmgr.pkNonMemGroupReserve and nmgr.pkNonMemberGroup= nmg.pkNonMemberGroup and cii.pkCIImplement =rp.pkCIImplement and rr.pkRoomRegister=rp.pkRoomRegister "
				+" and rr.checkInPrice is not null and cii.pkServicePoint= :pkServicePoint and nmg.checkOutDate between :start and :end and nmg.tourType = :tourType and nmg.status='CheckIn';";
		@SuppressWarnings("unchecked")
		List<Object[]> objects = getSession().createNativeQuery(sql)
				.setParameter("pkServicePoint", pkServicePoint)
				.setParameter("start", start)
				.setParameter("end", end)
				.setParameter("tourType", tourType.name()).list();
		return countSum(objects,start,end);
	}

	@Override
	public Double singleReceived(Long pkServicePoint, Date start, Date end,TourType tourType) {
		String sql = "select sum(cos.room) from travel_checkinimplement cii,travel_nonmemgroupreserve nmgr,travel_nonmembergroup nmg,travel_roomregister rr,travel_roompersonalInfo rp,travel_checkoutsettlement cos,travel_checkoutdeal cod "
				+" where  cii.pkNonMemGroupReserve=nmgr.pkNonMemGroupReserve and nmgr.pkNonMemberGroup= nmg.pkNonMemberGroup and cii.pkCIImplement =rp.pkCIImplement and rr.pkRoomRegister=rp.pkRoomRegister and rp.pkCheckOutSettlement=cos.pkCheckOutSettlement and cii.pkCIImplement=cod.pkCheckInImplement "
				+" and rr.checkInPrice is not null and cii.pkServicePoint= :pkServicePoint and nmg.checkOutDate between :start and :end and nmg.tourType = :tourType and cod.status='"+CheckOutDeal.Status.Confirm.name()+"' ;";
		Query query = getSession().createNativeQuery(sql)
				.setParameter("pkServicePoint", pkServicePoint)
				.setParameter("start", start)
				.setParameter("end", end)
				.setParameter("tourType", tourType.name());
		
		return query.uniqueResult() == null? 0D:(Double) query.uniqueResult();
	}
	
	
	private int daysOfTwoDate(Date start, Date end) {
	       Calendar aCalendar = Calendar.getInstance();
	       aCalendar.setTime(start);
	       int day1 = aCalendar.get(Calendar.DAY_OF_YEAR);
	       aCalendar.setTime(end);
	       int day2 = aCalendar.get(Calendar.DAY_OF_YEAR);
	       return day2 - day1;
	}

	@Override
	public Double singleAppoint(Long pkServicePoint, Date start, Date end, TourType tourType) {
		String sql = "select nmgr.pkNonMemGroupReserve,sr.pkRoomType,sr.count,nmg.checkinDate,nmg.checkOutDate from travel_nonmemgroupreserve nmgr,travel_nonmembergroup nmg,travel_signuproom sr"
				+" where  nmgr.pkNonMemberGroup= nmg.pkNonMemberGroup and nmgr.pkNonMemGroupReserve=sr.pkNonMemGroupReserve "
				+" and nmg.pkServicePoint= :pkServicePoint and nmg.checkOutDate >= :start and nmg.checkinDate <= :end and nmg.tourType = :tourType and nmg.status='Reserve' group by nmgr.pkNonMemGroupReserve ;";
		@SuppressWarnings("unchecked")
		List<Object[]> objects = getSession().createNativeQuery(sql)
				.setParameter("pkServicePoint", pkServicePoint)
				.setParameter("start", start)
				.setParameter("end", end)
				.setParameter("tourType", tourType.name()).list();
		return countSingleAppointSum(objects,start,end);
	}

	private Double countSingleAppointSum(List<Object[]> objects, Date start, Date end) {
		Double sum =0D;
		for (Object[] obj : objects) {
			RoomTypePrice cond = new RoomTypePrice();
			Date checkin =(Date)obj[3];
			Date checkout =(Date)obj[4];
			if (checkin.before(start)) {
				PropertyUtils.setProperty(cond, "saleDate", start);
				PropertyUtils.setProperty(cond, "saleDateEnd", checkout);
			}else if (checkout.after(end)) {
				PropertyUtils.setProperty(cond, "saleDate", checkin);
				PropertyUtils.setProperty(cond, "saleDateEnd", end);
			}else{
				PropertyUtils.setProperty(cond, "saleDate", checkin);
				PropertyUtils.setProperty(cond, "saleDateEnd", checkout);
			}
			PropertyUtils.setProperty(cond, "roomType,pkRoomType", obj[1]);
			sum+=	(roomTypePriceManager.querySumRoomPrice(cond).getSumStandardPrice())*(Integer)obj[2];
		}
		return sum;
	}

	@Override
	public Double assAppoint(Long pkServicePoint, Date start, Date end, boolean alliance) {
		String sql = "select gr.pkGroupReserve, room.pkRoomType,count(room.pkRoomType),gr.checkinDate,gr.checkOutDate from travel_groupreserve gr,travel_planitemgroup pg,travel_group_roomregister tgr,travel_roomregister tr,bd_room room "
				+" where  gr.pkPlanItemGroup= pg.pkPlanItemGroup  and gr.pkGroupReserve=tgr.pkGroupReserve and tgr.pkRoomRegister=tr.pkRoomRegister and tr.pkRoom= room.pkRoom "
				+" and pg.pkServicePoint= :pkServicePoint and gr.checkOutDate >= :start and gr.checkinDate <= :end and pg.alliance = :alliance and pg.checkInStatus='Reserve' group by gr.pkGroupReserve;";
		@SuppressWarnings("unchecked")
		List<Object[]> objects = getSession().createNativeQuery(sql)
				.setParameter("pkServicePoint", pkServicePoint)
				.setParameter("start", start)
				.setParameter("end", end)
				.setParameter("alliance", alliance).list();
		return countAllAppointSum(objects,start,end,alliance);
	
	}

	private Double countAllAppointSum(List<Object[]> objects, Date start, Date end, Boolean alliance) {
		Double sum =0D;
		for (Object[] obj : objects) {
			RoomTypePrice cond = new RoomTypePrice();
			Date checkin =(Date)obj[3];
			Date checkout =(Date)obj[4];
			if (checkin.before(start)) {
				PropertyUtils.setProperty(cond, "saleDate", start);
				PropertyUtils.setProperty(cond, "saleDateEnd", checkout);
			}else if (checkout.after(end)) {
				PropertyUtils.setProperty(cond, "saleDate", checkin);
				PropertyUtils.setProperty(cond, "saleDateEnd", end);
			}else{
				PropertyUtils.setProperty(cond, "saleDate", checkin);
				PropertyUtils.setProperty(cond, "saleDateEnd", checkout);
			}
			PropertyUtils.setProperty(cond, "roomType,pkRoomType", obj[1]);
			if(alliance){
				sum+=	roomTypePriceManager.querySumRoomPrice(cond).getSumVipPrice();
			}else{
				sum+=	roomTypePriceManager.querySumRoomPrice(cond).getSumProtocolPrice();
			}
		}
		return sum;
	}
   
}
